﻿use master
if exists (select * from sysdatabase where name='ShopHoatuoi'
			drop database ShopHoatuoi)
go
create database ShopHoaTuoi on primary
(	
 name='HoaTuoi_data', 
 filename='C:\shophaotuoi\Shophoatuoi.mdf'
)
log on
(	
 name='HoaTuoi_log',
 filename='C:\shophaotuoi\Shophoatuoi.ldf'
)
use ShopHoaTuoi
Go

----Lenh tao bang
create table NhaCungCap
(
	MaNCC char(8),
	TenNCC nvarchar(30),
	DiaChi nvarchar(50),
	DienThoai varchar(11)

)
create table DVTinh
(
	MaDVT char (8),
	TenDVT nvarchar(20)
)
Go
create table KhachHang
(
	TenDN nvarchar(100) not null,
	TenKH nvarchar(100) not null,
	MatKhau nvarchar(30) not null,
	DiaChiKH nvarchar(50),
	SoDT varchar(11),
	MaCN char(4) not null,
	Email varchar(100) 
)

Go
create table Hoa
(
	MaHoa char(6),
	TenHoa nvarchar(50),
	Nguongoc nvarchar(1000),
	YNghia nvarchar(4000),
	GiaHoa float,
	HinhAnh varchar(100)
)
create table CTLoaihoa
(
	MaLoai char(4),
	MaHoa char(6),
	Soluong int,
	primary key(MaLoai,MaHoa)
)
Go
create table Loaihoa
(
	MaLoai	char(6),
	TenLoai nvarchar (100)
)
Go
create table HDNhap
(
	SoHDN char(6),
	MaNCC char(8),
	NgayNhap varchar(10)
)
Go
create table CTHDNhap
(
	SoHDN char(6),
	MaHoa char(6),
	SlNhap int,
	DgNhap float,
	primary key(SoHDN,MaHoa)
)
Go

create table HDXuat
(
	SoHDX char(6) not null,
	TenDN nvarchar(100) not null,
	NgayXuat varchar(10)

)

Go

create table CTHDXuat
(
	SoHDX char(6),
	MaHoa char(6),
	SlXuat int,
	DgXuat float,
	primary key(SoHDX,MaHoa)
)
Go

create table NhomChucNang
(
	MaCN char(4),
	TenChucNang nvarchar(50),
	
)
Go
create table TinTuc
(
	MaTT char (7) not null,
	TenTT nvarchar(500) not null,
	NoiDung varchar(100),
	NgayDang varchar(10),
	MaLoai char(8) not null
)
create table LoaiTT
(
	MaLoai char(8) not null,
	TenLoai nvarchar(100) not null
)

--LENH RÀNG BUỘC KHÓA NGOẠI-----
alter table TinTuc
   add constraint FK_TinTuc_LoaiTT foreign key (MaLoai)
      references LoaiTT(MaLoai)
      on update cascade
      

alter table HDNhap
   add constraint FK_HDNhap_NhaCungCap foreign key (MaNCC)
      references NhaCungCap(MaNCC)
      on update cascade
     

alter table HDXuat
   add constraint FK_HDXuat_KhachHang foreign key (TenDN)
      references KhachHang(TenDN)
      on update cascade            

alter table KhachHang
   add constraint FK_NhomChucNang_KhachHang foreign key (MaCN)
      references NhomChucNang(MaCN)
      on update cascade

alter table HDNhap
   add constraint FK_HDNhap_DVTinh foreign key (MaDVT)
      references DVTinh(MaDVT)
      on update cascade

alter table HDXuat
   add constraint FK_HDXuat_DVTinh foreign key (MaDVT)
      references DVTinh(MaDVT)
      on update cascade



